"""Performance record service for business logic."""

import io
from datetime import date, datetime
from typing import Any, Optional

import pandas as pd
from fastapi import UploadFile
from sqlalchemy.orm import Session

from ..models.performance_record import PerformanceRecord
from ..models.user import User
from ..repositories.performance_record_repository import PerformanceRecordRepository
from ..schemas.performance_record import (
    PerformanceImportResult,
    PerformanceMonthlySummary,
)


class PerformanceService:
    """Service for performance record operations."""

    def __init__(self, db: Session):
        self.db = db
        self.repo = PerformanceRecordRepository(db)
        # Use synchronous DB queries for user operations (avoid async repository mismatch)

    async def import_from_excel(self, file: UploadFile) -> PerformanceImportResult:
        """Import performance records from Excel file.

        Expected Excel columns:
        - 姓名 (Name)
        - 日期 (Date)
        - 成交次数 (Deals Count)
        - 陪同看房次数 (Showings Count)
        - 新增客户 (New Customers)
        - 客户回访次数 (Follow-up Visits)

        Args:
            file: Uploaded Excel file

        Returns:
            Import result with success/error counts
        """
        errors = []
        success_count = 0

        try:
            # Read Excel file
            contents = await file.read()
            df = pd.read_excel(io.BytesIO(contents))

            # Normalize column names
            df.columns = df.columns.str.strip()

            # Map Chinese column names to English
            column_mapping = {
                "姓名": "name",
                "日期": "record_date",
                "成交次数": "deals_count",
                "陪同看房次数": "showings_count",
                "新增客户": "new_customers_count",
                "客户回访次数": "followup_visits_count",
            }

            # Check if columns exist
            missing_cols = set(column_mapping.keys()) - set(df.columns)
            if missing_cols:
                raise ValueError(f"Missing required columns: {', '.join(missing_cols)}")

            df = df.rename(columns=column_mapping)

            # Process each row
            for index, row in df.iterrows():
                try:
                    row_num = int(index) + 2
                    # Validate and parse data
                    name = str(row["name"]).strip()
                    if not name:
                        errors.append(f"Row {row_num}: 姓名不能为空")
                        continue

                    # Parse date
                    record_date = self._parse_date(row["record_date"])
                    if not record_date:
                        errors.append(f"Row {row_num}: 无效的日期格式")
                        continue

                    # Parse counts (default to 0 if empty)
                    deals_count = int(row.get("deals_count", 0) or 0)
                    showings_count = int(row.get("showings_count", 0) or 0)
                    new_customers_count = int(row.get("new_customers_count", 0) or 0)
                    followup_visits_count = int(row.get("followup_visits_count", 0) or 0)

                    # Validate counts are non-negative
                    if any(
                        c < 0
                        for c in [
                            deals_count,
                            showings_count,
                            new_customers_count,
                            followup_visits_count,
                        ]
                    ):
                        errors.append(f"Row {row_num}: 次数不能为负数")
                        continue

                    # Find or create user by name (sync query)
                    user = self._get_user_by_name(name)
                    if not user:
                        # Create new user if not exists
                        user = self._create_user_from_name(name)

                    # Upsert performance record
                    self.repo.upsert(
                        user_id=user.id,
                        record_date=record_date,
                        deals_count=deals_count,
                        showings_count=showings_count,
                        new_customers_count=new_customers_count,
                        followup_visits_count=followup_visits_count,
                    )

                    success_count += 1

                except (ValueError, KeyError, AttributeError) as e:
                    errors.append(f"Row {row_num}: {str(e)}")

            return PerformanceImportResult(
                total_rows=len(df),
                success_count=success_count,
                error_count=len(errors),
                errors=errors,
            )

        except (pd.errors.ParserError, pd.errors.EmptyDataError, ValueError, IOError) as e:
            return PerformanceImportResult(
                total_rows=0, success_count=0, error_count=1, errors=[f"文件解析失败: {str(e)}"]
            )

    def get_records(
        self,
        user_id: Optional[int] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
    ) -> list[PerformanceRecord]:
        """Get performance records with filters."""
        return self.repo.get_by_date_range(user_id, start_date, end_date)

    def get_monthly_summary(
        self, year: int, month: int, user_id: Optional[int] = None
    ) -> list[PerformanceMonthlySummary]:
        """Get monthly performance summary."""
        results = self.repo.get_monthly_summary(year, month, user_id)
        return [PerformanceMonthlySummary(**r) for r in results]

    def get_top_earners(self, year: int, month: int, limit: int = 10) -> list[dict[str, Any]]:
        """Get top earners for a month."""
        return self.repo.get_top_earners(year, month, limit)

    def delete_record(self, record_id: int) -> bool:
        """Delete a performance record."""
        record = self.repo.get(record_id)
        if record:
            self.repo.delete(record_id)
            return True
        return False

    def _get_user_by_name(self, name: str) -> User | None:
        """Sync helper: get user by full name."""
        return self.db.query(User).filter(User.full_name == name).first()

    @staticmethod
    def _parse_date(value: Any) -> Optional[date]:
        """Parse date from various formats."""
        if pd.isna(value):
            return None

        if isinstance(value, datetime):
            return value.date()
        if isinstance(value, date):
            return value

        # Try parsing string
        try:
            return pd.to_datetime(value).date()  # type: ignore[no-any-return]
        except (ValueError, TypeError, pd.errors.OutOfBoundsDatetime):
            return None

    def _create_user_from_name(self, name: str) -> User:
        """Create a new user from name (for import purposes).

        Note: This creates a minimal user record. In production,
        you may want to require users to be pre-registered.

        Args:
            name: User's full name

        Returns:
            Created User instance
        """
        # Generate a placeholder email
        email = f"{name.replace(' ', '_').lower()}@placeholder.local"

        user = User(
            email=email,
            hashed_password="",  # No password - import-only user
            full_name=name,
            role="consultant",
            is_active=True,
        )
        self.db.add(user)
        self.db.commit()
        self.db.refresh(user)
        return user
